package com.zk.atm.utils;

import com.bpms.common.JdbcWorker;
import com.bpms.common.VO;
import com.bpms.common.VOList;
import com.zk.atm.forms.AtmpDeviceStatusForm;

/**
 * @author zengqun
 * @remark 定义定时任务中的sql常量名,sql定义
 * @date 2012-9-29
 * @version 1.0
 */
public class ConstantsAtmpSql {

	/**
	 * @author zengqun
	 * @remark 定义定时任务中的:设备最新上报时间--与截止到当前时间的,时间差值,以分钟计算
	 *         常量名
	 * @date 2012-9-29
	 * @version 1.0
	 */
	public static final String ATMP_CURRENT_STATUS_TASK_DIFFER_MINUTES_SQL_NAME="differ_minutes_sql";
	
	/**
	 * @author zengqun
	 * @remark 定义定时任务中的:设备最新上报时间--与截止到当前时间的,时间差值,以分钟计算
	 *         sql定义
	 * @date 2012-9-29
	 * @version 1.0
	 */
	public static  String ATMP_CURRENT_STATUS_TASK_DIFFER_MINUTES_SQL= 
		" update atmp_device_status_current adsc1                  "+
	 "    set adsc1.minutes_from_time_mark_to_now =                  "+
	 "        round((select (abs(sysdate -                           "+
	 "                     (select adsc.time_mark                    "+
	 "                        from atmp_device_status_current adsc   "+
	 "                       where adsc.deviceip = ?)) * 24 * 60) as minutes   "+
	 "           from dual),2)             "+
	 "  where adsc1.deviceip = ?  ";
	
	/**
	 * @author zengqun
	 * @remark 定义定时任务中的:设备全部ip的sql常量名
	 * @date 2012-9-29
	 * @version 1.0
	 */
	public static final String ATMP_CURRENT_STATUS_TASK_DEVICE_IP_LIST_SQL_NAME="device_sql";
	
	/**
	 * @author zengqun
	 * @remark 定义定时任务中的:设备全部ip的sql定义
	 * @date 2012-9-29
	 * @version 1.0
	 */
	public static  String ATMP_CURRENT_STATUS_TASK_DEVICE_IP_LIST_SQL="select adsc.deviceip from atmp_device_status_current adsc";

	/**
	 * @author zengqun
	 * @remark 定义定时任务中的:更新设备状态为:报停状态的sql定义
	 * @date 2012-9-29
	 * @version 1.0
	 */
	public static final String ATMP_CURRENT_STATUS_TASK_UPDATE_REPORT_QUIT_SQL_NAME="report_quit_sql";
	
	/**
	 * @author zengqun
	 * @remark 定义定时任务中的:超出60分钟未上报状态,则更新设备状态为:报停状态的sql定义
	 * @date 2012-9-29
	 * @version 1.0
	 */
	public static  String ATMP_CURRENT_STATUS_TASK_UPDATE_REPORT_QUIT_SQL= "  update atmp_device_status_current adsc1 set adsc1.servicestatus=? " +
	"  where 1=1 and to_number(adsc1.minutes_from_time_mark_to_now)>?";
	
	/**
	 * @author zengqun
	 * @remark 定义定时任务中的:更新设备状态为:报停状态的界限值,超出60分钟时间范围的分钟数值.
	 * @date 2012-9-29
	 * @version 1.0
	 */
	public static String ATMP_CURRENT_STATUS_TASK_UPDATE_REPORT_QUIT_MINUTES_VALUE="60";
	
	/**
	 * @author zengqun
	 * @remark 定义定时任务中的:时间差的参数名
	 * @date 2012-9-29
	 * @version 1.0
	 */
	public static String ATMP_CURRENT_STATUS_TASK_UPDATE_REPORT_QUIT_MINUTES_NAME="device_status_report_quit_minutes";
	
	/**
	 * @author zengqun
	 * @remark 机构,设备,设备交易记录加数据权限
	 * @date 2012-11-19
	 * @version 1.0
	 */
	public static String ATMP_PERM_SQL= 
		" select '0'||max(sys_connect_by_path(ao.org_id,',')) path "
		+" from atmp_org ao        "
		+" start with ao.org_id in( "
		+"  select bu.orgid from bpm_user bu "
		+"   where bu.userid='#userid#' "
		+" ) "
		+" connect by prior ao.org_id=ao.parent_org_id ";
//	 "          (select ao.org_id                                       "+
//	 "             from atmp_org ao                                     "+
//	 "            where 1 = 1                                           "+
//	 "              and ao.parent_org_id in                             "+
//	 "                  (select bu.orgid                                "+
//	 "                     from bpm_user bu, atmp_org ao1               "+
//	 "                    where bu.orgid = ao1.org_id                   "+
//	 "                      and bu.userid = '#userid#')                      "+
//	 "           union                                                  "+
//	 "           select ao2.org_id                                      "+
//	 "             from atmp_org ao2                                    "+
//	 "            where ao2.org_id in (select bu.orgid                  "+
//	 "                                   from bpm_user bu, atmp_org ao1 "+
//	 "                                  where bu.orgid = ao1.org_id     "+
//	 "                                    and bu.userid = '#userid#'))       ";
	
	/**
	 * @author zengqun
	 * @remark 机构,设备,设备记录加数据权限
	 * @date 2012-11-19
	 * @version 1.0
	 */
	public static String ATMP_PERM_ORG_CODE_SQL=        //这里采用了sys_connect_by_path参数,依次可取
		                                           //出最大级别的数据
		"( select '0'||max(sys_connect_by_path(ao.org_code,',')) path "
		+" from atmp_org ao        "
		+" start with ao.org_id in( "
		+"  select bu.orgid from bpm_user bu "
		+"   where bu.userid='#userid#' "
		+" ) "
		+" connect by prior ao.org_id=ao.parent_org_id )";
	//下面这条sql只能联查2级,第3级无法取出code
//		"                                          "+   
//	 "          (select ao.org_code                                       "+
//	 "             from atmp_org ao                                     "+
//	 "            where 1 = 1                                           "+
//	 "              and ao.parent_org_id in                             "+
//	 "                  (select bu.orgid                                "+
//	 "                     from bpm_user bu, atmp_org ao1               "+
//	 "                    where bu.orgid = ao1.org_id                   "+
//	 "                      and bu.userid = '#userid#')                      "+
//	 "           union                                                  "+
//	 "           select ao2.org_code                                      "+
//	 "             from atmp_org ao2                                    "+
//	 "            where ao2.org_id in (select bu.orgid                  "+
//	 "                                   from bpm_user bu, atmp_org ao1 "+
//	 "                                  where bu.orgid = ao1.org_id     "+
//	 "                                    and bu.userid = '#userid#'))       ";

	/**
	 * @author zengqun
	 * @remark 获取归属的org_ids,用于联查设备,机构,设备状态监控表的数据控制
	 * @date 2012-11-20
	 * @version 1.0
	 */
	public static String getBelongOrgIds(AtmpDeviceStatusForm form) throws Exception {
		String belong_org_ids="";
		JdbcWorker jdbc=null;
		try {
			String userid=form.getUserid();
			String sql=ATMP_PERM_SQL.replaceAll("#userid#", userid);
			jdbc=new JdbcWorker();
			VOList volist=jdbc.readAll(new VO(), sql);
			if(volist!=null&&volist.listData.size()>0){
				VO vo=(VO) volist.listData.get(0);
				belong_org_ids=StringUtil.getPropertyValue(vo.getAttr("path"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(jdbc!=null){
				jdbc=null;
			}
		}
		
		return belong_org_ids;
	}
	
	/**
	 * @author zengqun
	 * @remark 获取归属的org_codes,用于联查设备监控交易记录表,加数据权限控制
	 * @date 2012-11-20
	 * @version 1.0
	 */
	public static String getBelongOrgCodes(AtmpDeviceStatusForm form) throws Exception {
		String belong_org_ids="";
		JdbcWorker jdbc=null;
		try {
			String userid=form.getUserid();
			String sql=ATMP_PERM_ORG_CODE_SQL.replaceAll("#userid#", userid);
			jdbc=new JdbcWorker();
			VOList volist=jdbc.readAll(new VO(), sql);
			if(volist!=null&&volist.listData.size()>0){
				VO vo=(VO) volist.listData.get(0);
				belong_org_ids=StringUtil.getPropertyValue(vo.getAttr("path"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(jdbc!=null){
				jdbc=null;
			}
		}
		
		return belong_org_ids;
	}
}
